from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from tkinter import font as tkfont
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from mini_tools.uitool import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import time

class ShopeeADKWFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='虾皮新拓展词功能', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        ExpandKW(self).pack(fill=X,padx=3,pady=3)
        
class ExpandKW(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.searchStr1=StringVar(value='正在获取...')
        self.searchStr2=StringVar(value='正在获取...')
        self.ppdStr1=StringVar(value='正在获取...')
        self.ppdStr2=StringVar(value='正在获取...')
        self.ppdStr3=StringVar(value='正在获取...')
        self.CDstr=StringVar(value='正在获取状态')
        self.SDstr=StringVar(value='正在获取状态')
        self.createWidgets()
        Thread(target=self.get_search_count).start()
        Thread(target=self.getcount).start()
        Thread(target=self.get_pp_count).start()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X)

        f01=Frame(f0)
        f01.pack(fill=X,pady=2)
        self.btn_add_allkw=Button(f01,text=f'添加所有在用广告出单词',command=lambda:Thread(target=self.add_allkw).start(),fg='#ffffff',bg='green')
        self.btn_add_allkw.pack(side=LEFT,padx=2)

        f02=Frame(f0)
        f02.pack(fill=X,pady=2)
        self.btn_cd_cj=Button(f02,text=f'出单词1~6词采集',command=lambda:Thread(target=self.start_task,args=('cd',)).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_cd_cj.pack(side=LEFT,padx=2,fill=X)
        Label(f02, textvariable=self.CDstr,fg='red',width=30,anchor="w").pack(side=LEFT,padx=2,fill=X)

        f03=Frame(f0)
        f03.pack(fill=X,pady=2)

        self.btn_cdcj_search=Button(f03,text=f'采集出单词拓展词搜索量',command=lambda:Thread(target=self.cj_search,args=('cd',)).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_cdcj_search.pack(side=LEFT,padx=2)
        Label(f03, textvariable=self.searchStr1,fg='#871F78',anchor="w",width=20).pack(side=LEFT,padx=2,fill=X)

        f04=Frame(f0)
        f04.pack(fill=X,pady=2)
        self.btn_cdcj_ppd=Button(f04,text=f'采集出单词拓展词DeepSeek匹配度',command=lambda:Thread(target=self.cj_ppd,args=('cd',)).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_cdcj_ppd.pack(side=LEFT,padx=2)
        Label(f04, textvariable=self.ppdStr1,fg='#871F78',anchor="w",width=40).pack(side=LEFT,padx=2,fill=X)



        f05=Frame(f0)
        f05.pack(fill=X,pady=2)
        label_font = tkfont.Font(family="微软雅黑", size=10, weight="bold")
        Label(f05, text='输入单个或者多个词/词组(一行一个词/词组):',font=label_font).pack(side=LEFT, padx=2)

        
        f06=Frame(f0)
        f06.pack(fill=X)
        self.KWIpt = Text(f06,height=5,width=50)
        self.KWIpt.pack(side=LEFT)
        Label(f06, text='PID:',font=label_font).pack(side=LEFT, padx=2)
        self.PIDIpt=Entry(f06)
        self.PIDIpt.pack(side=LEFT, padx=2)


        f07=Frame(f0)
        f07.pack(fill=X)
        self.btn_add_onekw=Button(f07,text=f'手动添加单词/词组',command=lambda:Thread(target=self.add_onekw).start(),fg='#ffffff',bg='green')
        self.btn_add_onekw.pack(side=LEFT,padx=2)


        self.btn_sd_cj=Button(f07,text=f'指定词1~6词采集',command=lambda:Thread(target=self.start_task,args=('zd',)).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_sd_cj.pack(side=LEFT,padx=2,fill=X)
        Label(f07, textvariable=self.SDstr,fg='red',width=30,anchor="w").pack(side=LEFT,padx=2,fill=X)

        f08=Frame(f0)
        f08.pack(fill=X,pady=2)
        self.btn_sdcj_search=Button(f08,text=f'采集指定词拓展词搜索量',command=lambda:Thread(target=self.cj_search,args=('sd',)).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_sdcj_search.pack(side=LEFT,padx=2)
        Label(f08, textvariable=self.searchStr2,fg='#871F78',anchor="w",width=20).pack(side=LEFT,padx=2,fill=X)

        f09=Frame(f0)
        f09.pack(fill=X,pady=2)
        self.btn_sdcj_ppd=Button(f09,text=f'采集指定词拓展词DeepSeek匹配度',command=lambda:Thread(target=self.cj_ppd,args=('sd',)).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_sdcj_ppd.pack(side=LEFT,padx=2)
        Label(f09, textvariable=self.ppdStr2,fg='#871F78',anchor="w",width=40).pack(side=LEFT,padx=2,fill=X)


        f00=Frame(f0)
        f00.pack(fill=X,pady=2)
        self.btn_to_YNADSCV=Button(f00,text='将匹配度符合的长尾词添加到精准匹配词表',command=lambda:Thread(target=self.to_ynadcsv).start(),fg='#ffffff',bg='green')
        self.btn_to_YNADSCV.pack(side=LEFT,padx=2)
        Label(f00, textvariable=self.ppdStr3,fg='#871F78',anchor="w").pack(side=LEFT,padx=2, pady=2,fill=X)

        f20=Frame(f0)
        f20.pack(fill=X,pady=2)
        self.btn_end=Button(f20,text=f'关闭所有任务',command=lambda:Thread(target=self.end_task).start(),fg='#ffffff',bg='green')
        self.btn_end.pack(side=LEFT,padx=2,fill=X)


    def getcount(self):

        while True:
            try:

                sqlstr3=''' select 
                            count(case when is_cj_kw=0 and source_kw_type = '出单词' and is_has_cd=1 and ad_ended=0 then 1 end),
                            count(case when is_cj_kw=1 and source_kw_type = '出单词' and is_has_cd=1 then 1 end),
                            count(case when is_cj_kw=0 and source_kw_type = '指定词' and source_kw=kw then 1 end),
                            count(case when is_cj_kw=1 and source_kw_type = '指定词' then 1 end)
                            from [ShopeeeADTZKW] where wordnum<=6'''
                rs3=self.ms.ExecQuery(sqlstr3)
                cd_notcj,cd_cj,zd_notcj,zd_cj=rs3[0]
                sqlstr2='select id,state from task where id in (4,24)'
                rs2=self.ms.ExecQuery(sqlstr2)
                for tid,state in rs2:
                    if tid==4:
                        cbtn=self.btn_cd_cj
                        cstr=self.CDstr
                        cj_type='出单词'
                        ncj,yycj=cd_notcj,cd_cj
                    elif tid==24:
                        cbtn=self.btn_sd_cj
                        cstr=self.SDstr
                        cj_type='指定词'
                        ncj,yycj=zd_notcj,zd_cj
                    if state==0:
                        tj=''
                        if cj_type=='出单词':
                            tj='(在用广告)'
                        cstr.set(f'{cj_type},已采集:{yycj},需采集:{ncj}{tj}')
                        cbtn.config(state=NORMAL)
                    else:
                        cstr.set(f'{cj_type}正在运行,已采集:{yycj},未采集:{ncj}')
                        cbtn.config(state=DISABLED)

            except:
                pass
            time.sleep(10)
    
    def get_search_count(self):

        while True:
            try:
                rs0=self.ms.ExecQuery('select id,state,tname from task where id in (21,22,23,43)')
                btn_dict={
                    21:self.btn_cdcj_search,
                    22:self.btn_sdcj_search,
                    23:self.btn_cdcj_ppd,
                    43:self.btn_sdcj_ppd
                }

                for tid,tstate,tname in rs0:
                    cbtn= btn_dict[tid]
                    
                    if tstate==0:
                        cbtn.config(state=NORMAL)
                    else:
                        cbtn.config(state=DISABLED)

                sqlstr='''select 
                            count(case when searchcount is not null and source_kw_type = '出单词'  then 1 end),
                            count(case when searchcount is null and source_kw_type = '出单词' and ad_ended=0 then 1 end),
                            count(case when searchcount is not null and source_kw_type = '指定词' then 1 end),
                            count(case when searchcount is null and source_kw_type = '指定词' then 1 end),
                            count(case when deepseek is not null and source_kw_type = '出单词' then 1 end),
                            count(case when deepseek is null and source_kw_type = '出单词' and SearchCount>=50 then 1 end),
                            count(case when deepseek is not null and source_kw_type = '指定词' then 1 end),
                            count(case when deepseek is null and source_kw_type = '指定词' and SearchCount>=50 then 1 end)
                            from [ShopeeeADTZKW]
                    '''
                rs1=self.ms.ExecQuery(sqlstr)
                ttt=rs1[0]
                self.searchStr1.set(f'已采集:{ttt[0]},未采集:{ttt[1]}')
                self.searchStr2.set(f'已采集:{ttt[2]},未采集:{ttt[3]}')
                self.ppdStr1.set(f'已采集:{ttt[4]},未采集:{ttt[5]}(搜索量>=50)')
                self.ppdStr2.set(f'已采集:{ttt[6]},未采集:{ttt[7]}(搜索量>=50)')

     
            except:
                pass
            time.sleep(10)
    
    def get_pp_count(self):
        while True:
            sqlstr='''select 
                        count(case when deepseek='高度匹配' and source_kw_type = '出单词' and searchcount>=50 then 1 end),
                        count(case when deepseek='高度匹配' and source_kw_type = '出单词' and searchcount>=50 and is_add=1 then 1 end),
                        count(case when deepseek='比较匹配' and source_kw_type = '出单词' and SearchCount>=50 then 1 end),
                        count(case when deepseek='比较匹配' and source_kw_type = '出单词' and searchcount>=50 and is_add=1 then 1 end)
                        from [ShopeeeADTZKW]
                '''
            rs1=self.ms.ExecQuery(sqlstr)
            ttt=rs1[0]
            self.ppdStr3.set(f'在用广告出单词拓展词,高度匹配总数:{ttt[0]},已添加为精准词:{ttt[1]};比较匹配总数:{ttt[2]},已添加为精准词:{ttt[3]}')
            time.sleep(10)

    def start_task(self,tt):
        
        if not messagebox.askyesno('提示',f'确定开启拓展词词采集?'):
            return
        
        if tt=='cd':
            cbtn=self.btn_cd_cj
            kwt='出单词'
            wstr='and is_has_cd=1 and ad_ended=0'
            tid=4
        elif tt=='zd':
            cbtn=self.btn_sd_cj
            kwt='指定词'
            tid=24
            wstr=''
        cbtn.config(state=DISABLED)
        sqlstr1=f"select count(*) from ShopeeeADTZKW where is_cj_kw=0 and source_kw_type ='{kwt}' {wstr} and wordnum<=6"
        rs1=self.ms.ExecQuery(sqlstr1)
        if rs1[0][0]==0:
            print(f'当前数据表《ShopeeeADTZKW》没有{kwt}待采集')
            messagebox.showinfo('提示',f'当前数据库没有{kwt}待采集')
            cbtn.config(state=NORMAL)
            return
        


        aff1=self.ms.ExecNoQuery('update [task] set state=1 where [id]=?',(tid,))
        if aff1:
            print(f'开启任务成功,开始采集 {rs1[0][0]} 个{kwt}待采集')

        else:
            print('开启任务失败')
            messagebox.showerror('提示','开启任务失败')
            cbtn.config(state=NORMAL)
    
    def add_onekw(self):
        
        kwstr=self.KWIpt.get("1.0", "end").strip()
        pid=self.PIDIpt.get().strip()
        if not kwstr or not pid:
            messagebox.showinfo('提示','请先输入指定词/词组')
            return


        self.btn_add_onekw.config(state=DISABLED)


        kw_list=kwstr.split('\n')
        if not messagebox.askyesno('提示',f'是否将 {len(kw_list)} 个指定词导入到PID:{pid}'):
            return
        
        print(f'共 {len(kw_list)} 个指定词,开始导入...')
        rs_ptit=self.ms.ExecQuery('select title from campaign where product_id=?',(pid,))
        if len(rs_ptit)==0:
            ptit=None
        else:
            ptit=rs_ptit[0][0]
        time.sleep(3)
        j=1
        suc=0
        cur_time=datetime.now()
        
        for kw in kw_list:
            kw=kw.strip()
            rs=self.ms.ExecQuery('select is_has_cd from ShopeeeADTZKW where ProductID=? and kw=?',(pid,kw))
            if len(rs)>0:
                if rs[0][0]==0:
                    aff=self.ms.ExecNoQuery('update ShopeeeADTZKW set source_kw=?,source_kw_type=? where ProductID=? and kw=?',(kw,'指定词',pid,kw))
                    suc+=aff
                else:
                    print(f'第 {j} 个指定词:《{kw}》已经是出单词,跳过')
                j+=1
                continue
            word_num=len(kw.split())
            pdata={
                'ProductID':pid,
                'PTitle':ptit,
                'source_kw':kw,
                'source_kw_type':'指定词',
                'kw':kw,
                'WordNum':word_num,
                'YN_kw':kw,
                'update_time':cur_time,
                'upload_time':cur_time
            }

            zds=list(pdata.keys())
            params=list(pdata.values())
            zdstr=','.join(zds)
            wstr=','.join(['?' for _ in params])
            aff=self.ms.ExecNoQuery(f'insert into ShopeeeADTZKW({zdstr}) values({wstr})',params)
            print(f'第 {j} 个指定词:《{kw}》,存入表《ShopeeeADTZKW》状态:{aff}')
            j+=1
            suc+=aff          

        print(f'本次共{len(kw_list)}个指定词,成功导入:{suc}行')
        messagebox.showinfo('提示',f'本次共{len(kw_list)}个指定词,成功导入:{suc}行')

        self.btn_add_onekw.config(state=NORMAL)

    def add_allkw(self):
        
        
        sqlstr='''select * from YN_AD_CDC
                    where product_id in (select product_id from campaign where is_on=1)
                    and is_tz=0
                '''
        self.btn_add_allkw.config(state=DISABLED)
        rs_p=self.ms.ExecQuerydict(sqlstr)
        if len(rs_p)==0:
            messagebox.showinfo('提示','当前出单词表无新数据需要添加')
            self.btn_add_allkw.config(state=NORMAL)
            return
        
        # if not messagebox.askyesno('提示',f'共有 {len(rs_p)}  出单词可以添加,确定全部添加'):
        #     self.btn_add_allkw.config(state=NORMAL)
        #     return
        
        print(f'正在将 {len(rs_p)}  个出单词进行添加')
        suc=0
        aff2=0
        j=1
        cur_time=datetime.now()
        for r in rs_p:
            cd_id=r['id']
            pid=r['Product_ID']
            kw=r['Search_Query']
            word_num=len(kw.split())
            pdata={
                'ProductID':pid,
                'PTitle':r['Product_Name_Ad_Name'],
                'source_kw':kw,
                'source_kw_type':'出单词',
                'kw':kw,
                'WordNum':word_num,
                'YN_kw':kw,
                'is_has_cd':1,
                'update_time':cur_time,
                'upload_time':cur_time
            }
            print(f'第 {j}个,出单词:《{kw}》开始添加到新拓展词表《ShopeeeADTZKW》...')

            rs=self.ms.ExecQuery('select count(*) from ShopeeeADTZKW where ProductID=? and kw=?',(pid,kw))
            
            if rs[0][0]>0:
                aff=self.ms.ExecNoQuery('update ShopeeeADTZKW set is_has_cd=1 where is_has_cd=0 and ProductID=? and kw=?',(pid,kw))
                if aff>0:
                    aff2+=ms.ExecNoQuery('update YN_AD_CDC set is_tz=1 where [id]=?',(cd_id,))
                suc+=aff
            else:
                zds=list(pdata.keys())
                params=list(pdata.values())
                zdstr=','.join(zds)
                wstr=','.join(['?' for _ in params])
                sql_str=f'insert into ShopeeeADTZKW({zdstr}) values({wstr})'
                aff=ms.ExecNoQuery(sql_str,params)
                if aff>0:
                    aff2+=ms.ExecNoQuery('update YN_AD_CDC set is_tz=1 where [id]=?',(cd_id,))
                suc+=aff

            print(f'第 {j} 个,出单词:《{kw}》,添加到新拓展词表,标记YN_AD_CDC:{aff2}行,剩余:{len(rs_p)-j} 个')
            j+=1

        messagebox.showinfo('提示',f'上传一词完毕,成功上传{suc} 个出单词,共标记YN_AD_CDC:{aff2}行')

        self.btn_add_allkw.config(state=NORMAL)
    
    def cj_search(self,cty):

        if cty=='cd':
            cbtn=self.btn_cdcj_search
            stype='出单词'
            task_name='采集出单词拓展词搜索量'
            task_id=21
        elif cty=='sd':
            cbtn=self.btn_sdcj_search
            stype='指定词'
            task_name='采集指定词拓展词搜索量'
            task_id=22
        cbtn.config(state=DISABLED)

        
        rs=self.ms.ExecQuery(f"select count(*) from [ShopeeeADTZKW] where searchcount is null and ad_ended=0 and source_kw_type = '{stype}' ")
        if rs[0][0]==0:
            print(f'当前表《ShopeeeADTZKW》{stype}无搜索量待采集')
            
        else:

            aff= self.ms.ExecNoQuery('update task set state=1,tcount=? where id=?',(rs[0][0],task_id))
            if aff>0:
                print(f'任务《{task_name}》启动成功,待采集数:{rs[0][0]}')
            else:
                print(f'任务《{task_name}》启动失败')

        
        #cbtn.config(state=NORMAL)

    def cj_ppd(self,cty):

        if cty=='cd':
            cbtn=self.btn_cdcj_ppd
            stype='出单词'
            task_name='采集出单词拓展词匹配度'
            task_id=23
        elif cty=='sd':
            cbtn=self.btn_sdcj_ppd
            stype='指定词'
            task_name='采集指定词拓展词匹配度'
            task_id=43
        cbtn.config(state=DISABLED)

        
        rs=self.ms.ExecQuery(f"select count(*) from [ShopeeeADTZKW] where deepseek is null and source_kw_type = '{stype}' ")
        if rs[0][0]==0:
            print(f'当前表《ShopeeeADTZKW》{stype}待无匹配度待采集')
        else:

            aff= self.ms.ExecNoQuery('update task set state=1,tcount=? where id=?',(rs[0][0],task_id))
            if aff>0:
                print(f'任务《{task_name}》启动成功,待采集数:{rs[0][0]}')
            else:
                print(f'任务《{task_name}》启动失败')

    def to_ynadcsv(self):

        cbtn=self.btn_to_YNADSCV
        cbtn.config(state=DISABLED)

        sql_str=f'''
    select * from ShopeeeADTZKW as s left join campaign as c on s.ProductID=c.product_id
            where s.SearchCount>=50
            and s.source_kw_type in ('出单词','指定词')
            and s.is_add=0
            and
            ( ((LEN(TRIM(s.source_kw)) - LEN(REPLACE(TRIM(s.source_kw), ' ', '')) + 1)=1 and s.deepseek='高度匹配' )
                or
                ((LEN(TRIM(s.source_kw)) - LEN(REPLACE(TRIM(s.source_kw), ' ', '')) + 1)>=2 and s.deepseek in ('比较匹配','高度匹配') )
            )        
            '''
        rs=self.ms.ExecQuerydict(sql_str)
        print(f'共获取到 {len(rs)} 个可导入长尾词,开始导入...')
        cur_time=datetime.now()
        j=1
        suc=0
        fail=0
        ig_n=0
        for r in rs:
            if r['source_kw_type']=='出单词':
                if r['is_has_cd'] ==1:
                    kw_form='出单词表'
                else:
                    kw_form='新拓展词表出单词长尾词'
            elif r['source_kw_type']=='指定词':
                kw_form='新拓展词表指定词长尾词'
            
            pid=r['ProductID']
            kw=r['kw']
            rs1=self.ms.ExecQuery('select count(*) from YN_AD_CSV where Product_ID=? and Search_Query=?',(pid,kw))

            if rs1[0][0]==0:

                pdatas={
                    'BrowserID':r['bid'],
                    'DpName':r['bname'],
                    'GroupName':r['gname'],
                    'User_Name':r['UserName'],
                    'Shop_ID':r['shopid'],
                    'Product_Name_Ad_Name':r['PTitle'],
                    'Product_ID':pid,
                    'Search_Query':kw,
                    'Search_Volume':r['SearchCount'],
                    'uploadTime':cur_time,
                    'updateTime':cur_time,
                    'WordNum':r['WordNum'],
                    'deepseek':r['deepseek'],
                    'kwfrom':kw_form
                }
                zdstr=','.join(pdatas.keys())
                params=list(pdatas.values())
                wstr=','.join(['?' for _ in params])
                aff=self.ms.ExecNoQuery(f'insert into YN_AD_CSV({zdstr}) values({wstr})',params)
                if aff>0:
                    suc+=1
                    aff2=self.ms.ExecNoQuery('update ShopeeeADTZKW set update_time=?,is_add=1 where ProductID=? and kw=?',(cur_time,pid,kw))
                    print(f'第{j}个拓展词:《{kw}》,添加到《YN_AD_CSV》精准词表成功,标记状态:{aff2},任务剩余:{len(rs)-j}')
                else:
                    fail+=1
                    print(f'第{j}个拓展词:《{kw}》,添加到《YN_AD_CSV》精准词表失败,任务剩余:{len(rs)-j}')
            else:
                ig_n+=1
                aff2=self.ms.ExecNoQuery('update ShopeeeADTZKW set update_time=?,is_add=1 where ProductID=? and kw=?',(cur_time,pid,kw))
                print(f'第{j}个拓展词:《{kw}》,《YN_AD_CSV》精准词表已存在该词忽略,标记状态:{aff2}任务剩余:{len(rs)-j}')

            j+=1

        print(f'本次需导入{len(rs)}个数据到,导入精准词表,其中成功:{suc},失败:{fail},已存在忽略:{ig_n}')
        messagebox.showinfo('提示',f'本次需导入{len(rs)}个数据到,导入精准词表,其中成功:{suc},失败:{fail},已存在忽略:{ig_n}')
        cbtn.config(state=NORMAL)

    def end_task(self):
        self.btn_end.config(state=DISABLED)
        rs=self.ms.ExecQuery('select id,tname from task where state=1 and id in (4,21,22,23,24,43,44)')
        suc=0
        msg=''
        for tid,tname in rs:

            aff=self.ms.ExecNoQuery('update task set state=-1 where id=?',(tid))
            if aff>0:
                msg+=f'任务《{tname}》关闭成功\n'
            else:
                msg+=f'任务《{tname}》关闭失败\n'
            suc+=aff
        print(f'{msg},关闭了{suc}个任务')
        messagebox.showinfo('提示',f'{msg}关闭了{suc}个任务')
        self.btn_end.config(state=NORMAL)

import tkinter
list_win=tkinter.Tk()
list_win.wm_geometry("860x700")
list_win.title('印尼虾皮群控操作')
ShopeeADKWFra(list_win).pack(fill=X,padx=3,pady=3)
list_win.mainloop()